package com.geneqiao.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.geneqiao.jdbc.jpa.ProcdureParamater;
import com.geneqiao.jdbc.jpa.TableStruct;
import com.geneqiao.jdbc.pool.JDBCDataSource;
import com.geneqiao.jdbc.pool.JDBCMap;
import com.geneqiao.jdbc.pool.SelectConn;
import com.geneqiao.jdbc.util.DataUtils;
import com.geneqiao.jdbc.util.ObjectUtils;
import com.geneqiao.jdbc.util.ParameterType;
import com.geneqiao.jdbc.util.SQLType;

public abstract class AbstractSession
{
	private static final Logger logger = Logger.getLogger(AbstractSession.class);

	protected JDBCDataSource jdbcDataSource = null;
	protected static final String NULL = "null";
	private static SelectConn selectConn;
	private static final Object obj1 = new Object();

	// 获取带事务的连接
	public Connection getConnection() throws SQLException
	{
		Connection conn = jdbcDataSource.getConnection();
		conn.setAutoCommit(false);
		return conn;
	}

	public <T> List<T> Query(Connection conn, Class<T> t)
	{
		if (JDBCMap.findEntity(t))
		{
			if (logger.isDebugEnabled())
				logger.debug("Find the entity '" + t.getName() + "'");
			TableStruct struct = JDBCMap.getEntity(t);
			String sql = "select * from " + struct.getTableName();
			return Query(conn, t, sql, NULL);
		}
		logger.error("The annotation for '" + t.getName() + "' was not found");
		return null;
	}

	public <T> List<T> Query(Connection conn, Class<T> t, String sql)
	{
		return Query(conn, t, sql, NULL);
	}

	public <T> List<T> Query(Connection conn, Class<T> t, String sql, Object... objects)
	{
		try
		{
			return buildQuery(t, sql, conn, objects);
		}
		catch (SQLException e)
		{
			JDBCDataSource.rollback(conn);
			JDBCDataSource.close(conn);
			return null;
		}
	}

	public <T> List<T> Query(Class<T> t)
	{
		if (JDBCMap.findEntity(t))
		{
			if (logger.isDebugEnabled())
				logger.debug("Find the entity '" + t.getName() + "'");
			TableStruct struct = JDBCMap.getEntity(t);
			String sql = "select * from " + struct.getTableName();
			return Query(t, sql, NULL);
		}
		logger.error("The annotation for '" + t.getName() + "' was not found");
		return null;
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @return Model Collection
	 */
	public <T> List<T> Query(Class<T> t, String sql)
	{
		return Query(t, sql, NULL);
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @param autoMap
	 *            是否字段转换列名进行映射
	 * @return 查询的对象集合
	 */
	public <T> List<T> Query(Class<T> t, String sql, Object... objects)
	{
		try
		{
			synchronized (obj1)
			{
				if (selectConn == null)
					selectConn = jdbcDataSource.getSelectConnection();
			}
			return buildQuery(t, sql, selectConn.getConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("Get result faild => " + sql, e);
			return null;
		}
		finally
		{
			// jdbcDataSource.releaseConnection(conn);
		}
	}

	protected abstract <T> List<T> buildQuery(Class<T> t, String sql, Connection conn, Object... objects)
			throws SQLException;

	public <T> List<T> QueryList(Connection conn, String sql)
	{
		return QueryList(conn, sql, NULL);
	}

	public <T> List<T> QueryList(Connection conn, String sql, Object... objects)
	{
		try
		{
			return buildQueryList(sql, conn, objects);
		}
		catch (SQLException e)
		{
			JDBCDataSource.rollback(conn);
			JDBCDataSource.close(conn);
			logger.error("获取查询结果失败 =>" + sql, e);
			return null;
		}
	}

	/***
	 * 
	 * @param sql
	 *            查询的SQL
	 * @return 查询的对象集合
	 */
	public <T> List<T> QueryList(String sql)
	{
		return QueryList(sql, NULL);
	}

	/**
	 * 
	 * @param sql
	 * @param objects
	 *            parameters
	 * @return 查询的单列集合
	 */
	public <T> List<T> QueryList(String sql, Object... objects)
	{
		try
		{
			synchronized (obj1)
			{
				if (selectConn == null)
					selectConn = jdbcDataSource.getSelectConnection();
			}
			return buildQueryList(sql, selectConn.getConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("获取查询结果失败 =>" + sql, e);
			return null;
		}
	}

	protected abstract <T> List<T> buildQueryList(String sql, Connection conn, Object... objects) throws SQLException;

	/***
	 * 执行存储过程
	 * 
	 * @param sql
	 *            存储过程名
	 * @return 执行结果
	 */
	public boolean RunProcdure(String sql)
	{
		return RunProcdure(sql, null);
	}

	/***
	 * 执行存储过程
	 * 
	 * @param sql
	 *            存储过程名
	 * @param paras
	 *            存储过程参数
	 * @return 执行结果
	 */
	public boolean RunProcdure(String sql, List<ProcdureParamater> paras)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			CallableStatement cs = ObjectUtils.buildStatement(conn, sql);
			if (paras != null)
			{
				for (ProcdureParamater para : paras)
				{
					switch (para.getParameterType())
					{
					case IN:
						cs.setObject(para.getSort(), para.getValue());
						break;
					case OUT:
						cs.registerOutParameter(para.getSort(), Types.JAVA_OBJECT);
						break;
					default:
						cs.setObject(para.getSort(), para.getValue());
						cs.registerOutParameter(para.getSort(), Types.JAVA_OBJECT);
						break;
					}
				}
				// 执行存储过程
				cs.execute();
				for (ProcdureParamater para : paras)
				{
					if (para.getParameterType() == ParameterType.OUT || para.getParameterType() == ParameterType.INOUT)
					{
						para.setValue(cs.getObject(para.getSort()));
					}
				}
			}
			else
			{
				cs.execute();
			}
			cs.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("Procdure => " + sql);
				logger.debug("Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Run procdure faild =>" + sql, e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	public <T> T Single(Connection conn, Class<T> t, String sql)
	{
		return Single(conn, t, sql, NULL);
	}

	public <T> T Single(Connection conn, Class<T> t, String sql, Object... objects)
	{
		List<T> dataList = Query(conn, t, sql, objects);
		if (dataList != null && dataList.size() == 1) { return dataList.get(0); }
		return null;
	}

	public <T> T Single(Class<T> t, String sql)
	{
		return Single(t, sql, NULL);
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @param autoMap
	 *            是否字段转换列名进行映射
	 * @return 返回单个对象
	 * 
	 */
	public <T> T Single(Class<T> t, String sql, Object... objects)
	{
		List<T> dataList = Query(t, sql, objects);
		if (dataList != null && dataList.size() == 1) { return dataList.get(0); }
		return null;
	}

	public Map<String, Object> QueryMap(Connection conn, String sql)
	{
		return QueryMap(conn, sql, NULL);
	}

	public Map<String, Object> QueryMap(Connection conn, String sql, Object... objects)
	{
		try
		{
			return buildQueryMap(sql, conn, objects);
		}
		catch (SQLException e)
		{
			JDBCDataSource.rollback(conn);
			JDBCDataSource.close(conn);
			logger.error("Get map<k,v> faild =>" + sql, e);
			return null;
		}
	}

	public Map<String, Object> QueryMap(String sql)
	{
		return QueryMap(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return 查询的键值对
	 */
	public Map<String, Object> QueryMap(String sql, Object... objects)
	{
		try
		{
			synchronized (obj1)
			{
				if (selectConn == null)
					selectConn = jdbcDataSource.getSelectConnection();
			}
			return buildQueryMap(sql, selectConn.getConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("Get map<k,v> faild =>" + sql, e);
			return null;
		}
	}

	protected abstract Map<String, Object> buildQueryMap(String sql, Connection conn, Object... objects)
			throws SQLException;

	public Object getSingle(Connection conn, String sql)
	{
		return getSingle(conn, sql, NULL);
	}

	public Object getSingle(Connection conn, String sql, Object... objects)
	{
		try
		{
			return buildGetSingle(sql, conn, objects);
		}
		catch (SQLException e)
		{
			JDBCDataSource.rollback(conn);
			JDBCDataSource.close(conn);
			logger.error("Get single faild =>" + sql, e);
			return null;
		}
	}

	public Object getSingle(String sql)
	{
		return getSingle(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return 返回单个值
	 */
	public Object getSingle(String sql, Object... objects)
	{
		try
		{
			synchronized (obj1)
			{
				if (selectConn == null)
					selectConn = jdbcDataSource.getSelectConnection();
			}
			return buildGetSingle(sql, selectConn.getConnection(), objects);
		}
		catch (SQLException e)
		{
			logger.error("Get single faild =>" + sql, e);
			return null;
		}
	}

	protected abstract Object buildGetSingle(String sql, Connection conn, Object... objects) throws SQLException;

	public boolean Execute(Connection conn, String sql)
	{
		return Execute(conn, sql, NULL);
	}

	public boolean Execute(Connection conn, String sql, Object... objects)
	{
		try
		{
			return buildExecute(sql, conn, objects);
		}
		catch (SQLException e)
		{
			JDBCDataSource.rollback(conn);
			JDBCDataSource.close(conn);
			logger.error("Execute SQL faild => " + sql, e);
			return false;
		}
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return boolean
	 */
	public boolean Execute(String sql)
	{
		return Execute(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @param objects
	 *            执行sql语句对应的参数
	 * @return boolean
	 */
	public boolean Execute(String sql, Object... objects)
	{
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			return buildExecute(sql, conn, objects);
		}
		catch (SQLException e)
		{
			logger.error("Execute SQL faild => " + sql, e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	protected abstract boolean buildExecute(String sql, Connection conn, Object... objects) throws SQLException;

	public boolean Execute(List<String> sqls)
	{
		// 默认用事务执行
		return Execute(sqls, true);
	}

	/***
	 * 
	 * @param sqls
	 * @return
	 * @throws SQLException
	 */
	public boolean Execute(List<String> sqls, boolean withTran)
	{
		List<Sql> sqlList = new ArrayList<>();
		for (String sql : sqls)
		{
			sqlList.add(Sql.parse(sql));
		}
		return execute(sqlList, withTran);
	}

	public boolean execute(List<Sql> sqlList)
	{
		// 默认用事务执行
		return execute(sqlList, true);
	}

	public abstract boolean execute(List<Sql> sqlList, boolean withTran);

	public boolean save(Object obj)
	{
		if (obj instanceof List)
			return saveList((List<?>) obj);

		List<Object> objs = new ArrayList<>();
		objs.add(obj);
		return saveList(objs);
	}

	protected abstract boolean saveList(List<?> objs);

	public boolean update(Object obj, boolean notNull)
	{
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			return buildUpdate(conn, obj, notNull);
		}
		catch (SQLException e)
		{
			logger.error("Update object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	public boolean update(Connection conn, Object obj, boolean notNull)
	{
		try
		{
			return buildUpdate(conn, obj, notNull);
		}
		catch (SQLException e)
		{
			JDBCDataSource.rollback(conn);
			JDBCDataSource.close(conn);
			return false;
		}
	}

	public boolean updateNotNull(Object obj)
	{
		// 只更新不为NULL的字段
		return update(obj, true);
	}

	public boolean updateNotNull(Connection conn, Object obj)
	{
		// 只更新不为NULL的字段
		return update(conn, obj, true);
	}

	protected abstract boolean buildUpdate(Connection conn, Object obj, boolean notNull) throws SQLException;

	public boolean delete(Object obj)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			TableStruct map = DataUtils.buildSQLMap(obj, SQLType.DELETE);
			if (map == null || !map.hasPrimaryKey())
				return false;
			conn = jdbcDataSource.getConnection();
			PreparedStatement ps = ObjectUtils.buildStatement(conn, map.getBuildMap());
			Integer num = ps.executeUpdate();
			ps.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("Deleted, Rows => " + num + ", Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Delete object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.releaseConnection(conn);
		}
	}

	protected static long getNow()
	{
		return System.nanoTime();
	}
}
